package com.fit.utils.dev.code.generation.utils;

import com.fit.utils.se.EmptyUtils;
import com.fit.utils.se.IOUtil;
import lombok.extern.slf4j.Slf4j;

import java.sql.*;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

/**
 * 基本的dao
 *
 * @author hcl
 */
@Slf4j
public class BasicDao {
	
	private final static BasicDao dao = new BasicDao();
	

    /**
     * 通过sql查询
     *
     * @param sql
     * @param parms
     * @param conn
     * @return
     * @throws SQLException
     */
    protected List<Map<String, Object>> findBySql(String sql, Object[] parms, Connection conn) throws SQLException {
        return findBySql(sql, parms, conn, true);
    }

    /**
     * 通过sql查询
     *
     * @param sql
     * @param parms
     * @param conn
     * @return
     * @throws SQLException
     */
    protected List<Map<String, Object>> findBySql(String sql, Object[] parms, Connection conn, boolean close) throws SQLException {
        List<Map<String, Object>> result = new LinkedList<Map<String, Object>>();
        if (EmptyUtils.isEmpty(conn) || EmptyUtils.isEmpty(sql)) {
            return result;
        }
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = conn.prepareStatement(sql);

            if (EmptyUtils.isNotEmpty(parms)) {
                for (int i = 0; i < parms.length; i++) {
                    Object parm = parms[i];
                    ps.setObject(i + 1, parm);
                }
            }
            rs = ps.executeQuery();
            log.debug("执行sql:{}", sql);
            //检索此 ResultSet 对象的列的编号、类型和属性。
            ResultSetMetaData rsmd = rs.getMetaData();
            //得到当前的列数
            int colCount = rsmd.getColumnCount();
            while (rs.next()) {
                Map<String, Object> map = new HashMap<String, Object>();
                for (int i = 1; i <= colCount; i++) {//for循环控制列数
                    //得到当前列的列名
                    String name = rsmd.getColumnName(i).toLowerCase();
                    //得到当前列的类型,此处得到的是int类型，与Types中的常量相对应
//					int type = rsmd.getColumnType(i);
                    //得到当前列的值
                    Object val = rs.getObject(i);
                    map.put(name, val);
                }
                result.add(map);
            }
        } finally {
        	IOUtil.closeResources(rs ,ps , close ?conn : null);
        }
        return result;
    }

    /**
     * 通过sql查询
     *
     * @param sql
     * @param conn
     * @return
     * @throws SQLException
     */
    protected List<Map<String, Object>> findBySql(String sql, Connection conn) throws SQLException {
        return this.findBySql(sql, null, conn);
    }

    /**
     * 执行sql
     *
     * @param sql
     * @param parms
     * @param conn
     * @return
     * @throws SQLException
     */
    protected int execute(String sql, Object[] parms, Connection conn) throws SQLException {
        return execute(sql, parms, conn, true);
    }

    /**
     * 执行sql
     *
     * @param sql
     * @param parms
     * @param conn
     * @return
     * @throws SQLException
     */
    protected int execute(String sql, Object[] parms, Connection conn, boolean close) throws SQLException {
        if (EmptyUtils.isEmpty(conn)) {
            return 0;
        }
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = conn.prepareStatement(sql);
            if (EmptyUtils.isNotEmpty(parms)) {
                for (int i = 0; i < parms.length; i++) {
                    Object parm = parms[i];
                    ps.setObject(i + 1, parm);

                }
            }
            log.debug("执行sql:{}" , sql);
            return ps.executeUpdate();
        } finally {
            IOUtil.closeResources(rs ,ps , close ? conn : null);
        }
    }

    /**
     * 执行sql
     *
     * @param sql
     * @param conn
     * @return
     * @throws SQLException
     */
    protected int execute(String sql, Connection conn) throws SQLException {
        return this.execute(sql, null, conn);
    }

    /**
     * 执行sql
     *
     * @param sql
     * @param conn
     * @return
     * @throws SQLException
     */
    protected int execute(String sql, Connection conn, boolean close) throws SQLException {
        return this.execute(sql, null, conn, close);
    }


    /**
     * 批处理sql
     *
     * @param sql
     * @param parmList
     * @param conn
     * @return
     * @throws SQLException
     */
    protected int executeBatch(String sql, List<List<Object>> parmList, Connection conn) throws SQLException {
        if (EmptyUtils.isEmpty(conn)) {
            return 0;
        }

        int result = 0;
        PreparedStatement ps = null;
        try {
            conn.setAutoCommit(false);
            ps = conn.prepareStatement(sql);
            if (EmptyUtils.isNotEmpty(parmList)) {
                int j = 1;
                for (List<Object> parms : parmList) {
                    if (EmptyUtils.isNotEmpty(parms)) {
                        for (int i = 0; i < parms.size(); i++) {
                            Object obj = parms.get(i);
                            ps.setObject(i + 1, EmptyUtils.isNotEmpty(obj) ? obj : null);
                        }
                        ps.addBatch();
                    }

                    if (j % 200 == 0) {
                        int[] resu = ps.executeBatch();
                        conn.commit();
                        for (int r : resu) {
                            result += r;
                        }
                    }
                    j++;
                }
            }
        } finally {
            if (EmptyUtils.isNotEmpty(ps)) {
                int[] resu = ps.executeBatch();
                for (int r : resu) {
                    result += r;
                }
            }
            if (EmptyUtils.isNotEmpty(conn)) {
                conn.commit();
            }
            IOUtil.closeResources(ps ,conn);
        }
        return result;
    }

    /**
     * 批量保存
     *
     * @param sql
     * @param parmList
     * @param conn
     * @return
     * @throws SQLException
     */
    protected int executeBatch(String sql, List<Map<String, Object>> parmList, String[] parmKey, Connection conn) throws SQLException {
        if (EmptyUtils.isEmpty(conn)) {
            return 0;
        }

        int result = 0;
        PreparedStatement ps = null;
        try {
            conn.setAutoCommit(false);
            ps = conn.prepareStatement(sql);
            if (EmptyUtils.isNotEmpty(parmList)) {
                int j = 1;
                for (Map<String, Object> map : parmList) {
                    if (EmptyUtils.isNotEmpty(map)) {
                        for (int i = 0; i < parmKey.length; i++) {
                            Object obj = map.get(parmKey[i]);
                            ps.setObject(i + 1, EmptyUtils.isNotEmpty(obj) ? obj : null);
                        }
                        ps.addBatch();
                    }

                    if (j % 200 == 0) {
                        int[] resu = ps.executeBatch();
                        conn.commit();
                        for (int r : resu) {
                            result += r;
                        }
                    }
                    j++;
                }
            }
        } finally {
            if (EmptyUtils.isNotEmpty(ps)) {
                int[] resu = ps.executeBatch();
                for (int r : resu) {
                    result += r;
                }
            }
            if (EmptyUtils.isNotEmpty(conn)) {
                conn.commit();
            }
            IOUtil.closeResources(ps , conn );
        }
        return result;
    }

    /**
     * 获取dao对象
     * @return
     */
    public static BasicDao getDao(){
    	return dao;
    }

}
